package service;

import dao.db;
import pojo.Guestroominfo;
import pojo.room_used_times;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class roomService {
    public static int insert(Guestroominfo guestroominfo) {
        db d = new db();
        String sql = "INSERT INTO hotel.guestroominfo (grank, grent, gstate)\n" +
                "VALUES ('" + guestroominfo.getGrank() +
                "', " + guestroominfo.getGrent() +
                ", '" + guestroominfo.getGstate() +
                "');";
        System.out.println(sql);
        return d.update_execute(sql);
    }

    public static List<Guestroominfo> all() {
        db d = new db();
        String sql = "select * from Guestroominfo";
        ResultSet resultSet = d.executeQuery(sql);
        List<Object> list = db.selectList(new Guestroominfo(), resultSet);
        List<Guestroominfo> list1 = new ArrayList<>();
        list.forEach(i -> {
            list1.add((Guestroominfo) i);
        });
        return list1;
    }

    public static List<Guestroominfo> queryByid(int id) {
        db d = new db();
        String sql = "select * from Guestroominfo where id=" + id;
        ResultSet resultSet = d.executeQuery(sql);
        List<Object> list = db.selectList(new Guestroominfo(), resultSet);
        List<Guestroominfo> list1 = new ArrayList<>();
        list.forEach(i -> {
            list1.add((Guestroominfo) i);
        });
        return list1;
    }

    public static List<Guestroominfo> queryByTimeNotConfict(java.sql.Date startTime, java.sql.Date endTime) {
        db d = new db();
        String sql = "select * from guestroominfo where id not in\n" +
                "                                  (select room_id from consumption a\n" +
                "                                   where ( (a.start_time<='" + endTime +
                "' and a.end_time>='" + endTime +
                "')\n" +
                "                                       OR (a.end_time <= '" + startTime +
                "' and a.end_time<= '" + startTime +
                "') ))";
        ResultSet resultSet = d.executeQuery(sql);
        List<Object> list = db.selectList(new Guestroominfo(), resultSet);
        List<Guestroominfo> list1 = new ArrayList<>();
        list.forEach(i -> {
            list1.add((Guestroominfo) i);
        });
        return list1;
    }

    public static Guestroominfo queryByName(String name) {
        db d = new db();
        String sql = "select * from Guestroominfo where username like '%" + name + "%'";
        System.out.println(sql);
        ResultSet resultSet = d.executeQuery(sql);
        return (Guestroominfo) db.selectOneObject(new Guestroominfo(), resultSet);
    }

    public static int deleteByid(int id) {
        db d = new db();
        String sql = "delete from Guestroominfo where id=" + id;
        return d.update_execute(sql);
    }

    public static int updateByid(Guestroominfo Guestroominfo) {
        db d = new db();
        String sql = "";
        return d.update_execute(sql);
    }

    public static List<room_used_times> groupby() {
        db d = new db();
        String sql = "select consumption.room_id, room_type, count(consumption.room_id) as times from consumption group by room_id";
        ResultSet resultSet = d.executeQuery(sql);
        room_used_times r;
        List<room_used_times> list = new ArrayList<>();
        while (true) {
            try {
                if (!resultSet.next()) break;
                r = new room_used_times();
                r.setRoom_id(resultSet.getString(1));
                r.setRoom_type(resultSet.getString(2));
                r.setTimes(resultSet.getString(3));
                list.add(r);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return list;
    }

}
